package Demo;

import cn.yunhe.model.Books;
import cn.yunhe.model.Categories;
import cn.yunhe.model.Orders;
import cn.yunhe.model.Users;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.Calendar;
import java.util.List;

public class demo {
    Session session =null;
    @Before
    public void init(){
        Configuration configuration = new Configuration().configure();
        SessionFactory factory = configuration.buildSessionFactory();
        session = factory.openSession();
    }
@Test
public void test1() {
  //  Orders orders = (Orders) session.get(Orders.class, 1);
   // System.out.println(orders.getUsers().getName());
   //String hql ="select o.users from Orders o where o.id = 1";
    String hql = "select u from Users u, Orders o where u=o.users and o.id=1";
    List<Users> usersList = session.createQuery(hql).list();
    Users users = usersList.get(0);
   // session.close();
    System.out.println(users.getName());
}

@Test
public  void test2(){
        //String hql ="select u from Users u where u in(select o.users from Orders o)";
        String hql = "select  distinct  u from Users u inner join u.orderses";
        List<Users> list = session.createQuery(hql).list();
        for(Users u:list){
            System.out.println(u.getName()+"下的订单有：");
            for(Orders o:u.getOrderses()){
                System.out.println(o.getId()+","+o.getTotalprice());
            }
        }
}
@Test
public  void test3(){
        String hql = "select u from Users u left join u.orderses o where o is null";
        List<Users> users = session.createQuery(hql).list();
        for(Users u : users){
            System.out.println(u.getName());
        }
}
@Test
public  void test4(){
    Query query = session.getNamedQuery("myNameQuery");
    query.setString("title","%java%");
    query.setInteger("categoryid",2);
    List<Books> booksList = query.list();
    for(Books b :booksList){
        System.out.println(b.getTitle()+"，"+b.getCategoryid());
    }
}
@Test
public void test5(){
  // List<Object[]> list = session.createSQLQuery("select * from books").list();

  //  for (Object[] o : list) {
       // System.out.println(o[0] + "," + o[1]);
   List<Books> booksList = session.createSQLQuery( "select {b.*} from books b").addEntity("b",Books.class).list();
   for(Books b :booksList){
       System.out.println(b.getTitle());


        }
}
@Test
public void test(){

        List<Object[]> list = session.createSQLQuery("select {c.*},{b.*} from categories c,books b where c.id=b.categoryid")
                .addEntity("c",Categories.class).addEntity("b",Books.class).list();
        for(Object[] obj :list){
            Categories c = (Categories) obj[0];
            Books b = (Books) obj[1];
            System.out.println("类别"+c.getName()+"书籍"+b.getTitle());
        }

}
@Test
public  void test7(){
        List<Object[]>  list = session.getNamedQuery("mySqlQuery").list();
        for(Object[] obj :list){
            Categories c = (Categories) obj[0];
            Books b = (Books) obj[1];
            System.out.println("类别" + c.getName()+"书籍"+b.getTitle());
        }
}
@Test
public  void test8(){
    Criteria cri = session.createCriteria(Orders.class);
    List<Orders> orders = cri.add(Restrictions.gt("totalprice",300)).list();
    for(Orders o : orders){
        System.out.println(o.getTotalprice()+"，"+o.getId());
    }
}
@Test
public  void test9(){
        Criteria criteria = session.createCriteria(Orders.class);
    Calendar cal = Calendar.getInstance();
    cal.set(2010,1,1);
    List<Orders> list = criteria.add(Restrictions.gt("totalprice",300)).add(Restrictions.gt("orderdate",cal.getTime())).list();
     for(Orders o : list){
         System.out.println(o.getTotalprice()+"，"+o.getId());
     }
}
@Test
public  void test10(){
    Criteria criteria = session.createCriteria(Orders.class);
    List<Orders> list = criteria.addOrder(Order.desc("totalprice")).list();
    for (Orders orders : list) {
        System.out.println(orders.getTotalprice());
    }

}
@Test
public  void test11(){
        Criteria criteria = session.createCriteria(Orders.class);
        Users u = new Users();
        u.setId(1);
        List<Orders> list = criteria.add(Restrictions.eq("users",u)).list();
        for(Orders o : list){
            System.out.println(o.getUsers().getName()+"下的订单总价"+o.getTotalprice());
        }
}
@Test
public  void test12(){
        Criteria cri_order = session.createCriteria(Orders.class);
        Criteria cri_users = cri_order.createCriteria("users");
        cri_order.add(Restrictions.gt("totalprice",200));
        cri_users.add(Restrictions.eq("id",1));
        List<Orders> orders = cri_order.list();
        for(Orders orders1 :orders){
            System.out.println("客户" +orders1.getUsers().getName()+"订单总价"+orders1.getTotalprice());
        }
}

    @After
    public void destroy(){
       session.close();
    }
}
